#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dws_trade_user_course_order_1d="
insert overwrite table ${APP}.dws_trade_user_course_order_1d partition(dt='$do_date')
select
    user_id,
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    order_count_1d,
    order_original_amount_1d,
    coupon_reduce_amount_1d,
    order_total_amount_1d
from
(
    select
        user_id,
        course_id,
        count(*) order_count_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(nvl(split_coupon_reduce,0.0)) coupon_reduce_amount_1d,
        sum(split_final_amount) order_total_amount_1d
    from ${APP}.dwd_trade_order_detail_inc
    where dt='$do_date'
    group by user_id,course_id
)od
left join
(
    select
        id,
        course_name,
        subject_id,
        subject_name,
        category_id,
        category_name
    from ${APP}.dim_course_full
    where dt='$do_date'
)course
on od.course_id=course.id;
"
dws_trade_user_order_1d="
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt='$do_date')
select
    user_id,
    province_id,
    count(distinct(order_id)),
    count(distinct(order_id)),
    sum(split_original_amount),
    sum(nvl(split_coupon_reduce,0)),
    sum(split_final_amount)
from ${APP}.dwd_trade_order_detail_inc
where dt='$do_date'
group by user_id,province_id;
"
dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt='$do_date')
select
    user_id,
    count(*)
from ${APP}.dwd_trade_cart_add_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_payment_1d="
insert overwrite table ${APP}.dws_trade_user_payment_1d partition(dt='$do_date')
select
    user_id,
    count(distinct(order_id)),
    sum(split_final_amount)
from ${APP}.dwd_trade_pay_detail_suc_inc
where dt='$do_date'
group by user_id;
"
dws_trade_province_order_1d="
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt='$do_date')
select
    province_id,
    name,
    iso_code,
    iso_3166_2,
    order_count_1d,
    order_original_amount_1d,
    order_final_amount_1d
from
(
    select
        province_id,
        count(distinct(order_id)) order_count_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(split_final_amount) order_final_amount_1d
    from ${APP}.dwd_trade_order_detail_inc
    where dt='$do_date'
    group by province_id
)o
left join
(
    select
        id,
        name,
        iso_code,
        iso_3166_2
    from ${APP}.dim_province_full
    where dt='$do_date'
)p
on o.province_id=p.id;
"
dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(during_time),
    count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(during_time),
    count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"
dws_interaction_paper_course_test_exam_1d="
insert overwrite table ${APP}.dws_interaction_paper_course_test_exam_1d partition(dt='$do_date')
select
    t1.paper_id,
    t1.course_id,
    count(t1.user_id) user_count_1d,
    sum(t1.score) sum_score_1d,
    sum(t1.duration_sec) sum_duration_sec_1d,
    sum(if( score_level ='0-20',1,0)) 0_to_20_count,
    sum(if( score_level ='20-40',1,0)) 20_to_40_count,
    sum(if( score_level ='40-60',1,0)) 40_to_60_count,
    sum(if( score_level ='60-80',1,0)) 60_to_80_count,
    sum(if( score_level ='80-100',1,0)) 80_to_100_count
from
(
    select
        paper_id,
        user_id,
        course_id,
        score,
        case
            when score  >= 0 and score <20 then '0-20'
            when score  >= 20 and score <40 then '20-40'
            when score  >= 40 and score <60  then '40-60'
            when score  >= 60 and score <80  then '60-80'
            else '80-100' end score_level,
        duration_sec
    from ${APP}.dwd_interaction_test_exam_inc
    where dt='$do_date'
)t1
group by t1.paper_id,t1.course_id;
"
dws_interaction_question_test_exam_1d="
insert overwrite table ${APP}.dws_interaction_question_test_exam_1d partition(dt='$do_date')
select
    question_id,
    count(answer),
    sum(if(score=1,1,0))
from ${APP}.ods_test_exam_question_full
where dt='$do_date'
group by question_id;
"
dws_traffic_user_chapter_video_watch_video_1d="
insert overwrite table ${APP}.dws_traffic_user_chapter_video_watch_video_1d partition (dt='$do_date')
select
    user_id,
    chapter_id,
    video_id,
    duration_sec,
    course_id,
    count(1),
    sum(play_sec),
    max(position_sec),
    if(sum(play_sec) >= (duration_sec*0.9) and max(position_sec) >= (duration_sec*0.9),1,0)
from ${APP}.dwd_traffic_log_watch_video_inc
where dt='$do_date'
group by user_id,video_id,duration_sec,chapter_id,course_id;
"

case $1 in
    "dws_trade_user_course_order_1d" )
        hive -e "$dws_trade_user_course_order_1d"
    ;;
    "dws_trade_user_order_1d" )
        hive -e "$dws_trade_user_order_1d"
    ;;
    "dws_trade_user_cart_add_1d" )
        hive -e "$dws_trade_user_cart_add_1d"
    ;;
    "dws_trade_user_payment_1d" )
        hive -e "$dws_trade_user_payment_1d"
    ;;
    "dws_trade_province_order_1d" )
        hive -e "$dws_trade_province_order_1d"
    ;;
    "dws_traffic_session_page_view_1d" )
        hive -e "$dws_traffic_session_page_view_1d"
    ;;
    "dws_traffic_page_visitor_page_view_1d" )
        hive -e "$dws_traffic_page_visitor_page_view_1d"
    ;;
    "dws_interaction_paper_course_test_exam_1d" )
        hive -e "$dws_interaction_paper_course_test_exam_1d"
    ;;
    "dws_interaction_question_test_exam_1d" )
        hive -e "$dws_interaction_question_test_exam_1d"
    ;;
    "dws_traffic_user_chapter_video_watch_video_1d" )
        hive -e "$dws_traffic_user_chapter_video_watch_video_1d"
    ;;
    "all" )
        hive -e "$dws_trade_user_course_order_1d$dws_trade_user_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_payment_1d$dws_trade_province_order_1d$dws_traffic_session_page_view_1d$dws_traffic_page_visitor_page_view_1d$dws_interaction_paper_course_test_exam_1d$dws_interaction_question_test_exam_1d$dws_traffic_user_chapter_video_watch_video_1d"
    ;;
esac